using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace DalatCooking.Core
{
	public class User
	{
		private int userID;
		private string fullName;
		private string email;
		private string password;
		private string biography;
		private DateTime dateAdded;
		private string connectionString;

		public User()
		{
			connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
		}

		public User(int userID)
		{
            connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
			SqlConnection connection = new SqlConnection(connectionString);
			DataSet dataSet = new DataSet();

			connection.Open();

			SqlDataAdapter adapter = new SqlDataAdapter();
			SqlCommand command = new SqlCommand("Get_UserDetails", connection);
			command.Parameters.Add("@UserID", userID);
			command.CommandType = CommandType.StoredProcedure;

			adapter.SelectCommand = command;
			adapter.Fill(dataSet, "Users");

			command.Dispose();
			adapter.Dispose();
			connection.Close();

			LoadDetails(dataSet.Tables["Users"].Rows[0]);
			dataSet.Dispose();
		}

		public User(string email)
		{
			connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
			SqlConnection connection = new SqlConnection(connectionString);
			DataSet dataSet = new DataSet();

			connection.Open();

			SqlDataAdapter adapter = new SqlDataAdapter();
			SqlCommand command = new SqlCommand("Get_UserDetailsFromEmail", connection);
			command.Parameters.Add("@Email", email);
			command.CommandType = CommandType.StoredProcedure;

			adapter.SelectCommand = command;
			adapter.Fill(dataSet, "Users");

			command.Dispose();
			adapter.Dispose();
			connection.Close();
		
			LoadDetails(dataSet.Tables["Users"].Rows[0]);
			dataSet.Dispose();
		}

		private void LoadDetails (DataRow user)
		{
			userID = (int)user["UserID"];
			fullName = (string)user["FullName"];
			email = (string)user["Email"];
			password = (string)user["Password"];
			biography = (string)user["Biography"];
			dateAdded = (DateTime)user["DateAdded"];
		}

		// static method that returns all the users in database
		public static DataSet GetUsers()
		{
			string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
			SqlConnection connection = new SqlConnection(connectionString);
			DataSet dataSet = new DataSet();

			connection.Open();

			SqlDataAdapter adapter = new SqlDataAdapter();
			SqlCommand command = new SqlCommand("Get_Users", connection);
			
			adapter.SelectCommand = command;
			adapter.Fill(dataSet, "Users");

			command.Dispose();
			adapter.Dispose();
			connection.Close();

			return dataSet;
		}

		public static ArrayList GetUserRoles(int userID)
		{
			ArrayList roles = new ArrayList();
			
			string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
			SqlConnection connection = new SqlConnection(connectionString);

			connection.Open();

			SqlCommand command = new SqlCommand("Get_UserRoles", connection);
			command.Parameters.Add("@UserID", userID);
			command.CommandType = CommandType.StoredProcedure;
			SqlDataReader reader = command.ExecuteReader();

			while (reader.Read())
				roles.Add(reader.GetString(1));

			reader.Close();
			connection.Close();
			return roles;
		}

		public DataSet GetRoles()
		{
			connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
			SqlConnection connection = new SqlConnection(connectionString);
			DataSet dataSet = new DataSet();

			connection.Open();

			SqlDataAdapter adapter = new SqlDataAdapter();
			SqlCommand command = new SqlCommand("Get_UserRoles", connection);
			command.Parameters.Add("@UserID", userID);
			command.CommandType = CommandType.StoredProcedure;
			
			adapter.SelectCommand = command;
			adapter.Fill(dataSet, "Roles");

			command.Dispose();
			adapter.Dispose();
			connection.Close();

			return dataSet;
		}

		public int Add()
		{
			int rowsAffected;
			SqlConnection connection = new SqlConnection(connectionString);
			SqlCommand command = new SqlCommand("Create_User", connection);

			command.Parameters.Add("@FullName", fullName);
			command.Parameters.Add("@Email", email);
			command.CommandType = CommandType.StoredProcedure;

			connection.Open();
			
			try 
			{
				rowsAffected = command.ExecuteNonQuery();
			}
			catch (SqlException sqle)
			{
				// exception returns 2627 means that the email address 
				// has already been registerd
				if (sqle.Number == 2627)
					return -1;
				else
					throw sqle;
			}
			finally
			{
				command.Dispose();
				connection.Close();
			}

			return rowsAffected;
		}

		public bool Update()
		{
			int rowsAffected;
			SqlConnection connection = new SqlConnection(connectionString);
			SqlCommand command = new SqlCommand("Update_User", connection);

			command.Parameters.Add("@UserID", userID);
			command.Parameters.Add("@FullName", fullName);
			command.Parameters.Add("@Email", email);
			command.Parameters.Add("@Password", password);
			command.Parameters.Add("@Biography", biography);
			command.CommandType = CommandType.StoredProcedure;

			connection.Open();
			rowsAffected = command.ExecuteNonQuery();
			command.Dispose();
			connection.Close();

			return (rowsAffected == 1);
		}

		public bool Delete()
		{
			int rowsAffected;
			SqlConnection connection = new SqlConnection(connectionString);
			SqlCommand command = new SqlCommand("Delete_User", connection);
			command.Parameters.Add("@UserID", userID);
			command.CommandType = CommandType.StoredProcedure;

			connection.Open();
			rowsAffected = command.ExecuteNonQuery();
			command.Dispose();
			connection.Close();

			return (rowsAffected == 1);
		}

		public bool AddToRole(int roleID)
		{
			int rowsAffected;
			SqlConnection connection = new SqlConnection(connectionString);
			SqlCommand command = new SqlCommand("AddUserToRole", connection);
			command.Parameters.Add("@UserID", userID);
			command.Parameters.Add("@RoleID", roleID);
			command.CommandType = CommandType.StoredProcedure;

			connection.Open();
			rowsAffected = command.ExecuteNonQuery();
			command.Dispose();
			connection.Close();

			return (rowsAffected == 1);
		}

		public bool RemoveFromRole(int roleID)
		{
			int rowsAffected;
			SqlConnection connection = new SqlConnection(connectionString);
			SqlCommand command = new SqlCommand("RemoveUserFromRole", connection);
			command.Parameters.Add("@UserID", userID);
			command.Parameters.Add("@RoleID", roleID);
			command.CommandType = CommandType.StoredProcedure;

			connection.Open();
			rowsAffected = command.ExecuteNonQuery();
			command.Dispose();
			connection.Close();

			return (rowsAffected == 1);	
		}

		// Properties
		public int UserID
		{
			get { return userID; }
			set { userID = value; }
		}

		public string FullName
		{
			get { return fullName; }
			set { fullName = value; }
		}

		public string Email
		{
			get { return email; }
			set { email = value; }
		}

		public string Password
		{
			get { return password; }
			set { password = value; }
		}

		public string Biography
		{
			get { return biography; }
			set { biography = value; }
		}

		public DateTime DateAdded
		{
			get { return dateAdded; }
			set { dateAdded = value; }
		}
	}
}
